﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.IO;
using MySql.Data;
using MySql.Data.MySqlClient;
using ConfigLibrary;
using System.Runtime.Serialization.Formatters.Binary;
using System.Windows.Forms;

namespace Trident
{
  public class mysqlcon : Form
  {
    MySqlConnection conn = new MySqlConnection();
    MySqlCommandBuilder tricmdbldr = new MySqlCommandBuilder();
    MySqlCommand projcmd = new MySqlCommand();
    MySqlDataAdapter projda = new MySqlDataAdapter();
    MySqlCommandBuilder projbldr = new MySqlCommandBuilder();
    MySqlCommand contactscmd = new MySqlCommand();
    MySqlDataAdapter contactsda = new MySqlDataAdapter();
    MySqlCommand pcontcmd = new MySqlCommand();
    MySqlDataAdapter pcontda = new MySqlDataAdapter();
    MySqlCommandBuilder pcontbldr = new MySqlCommandBuilder();
    MySqlDataAdapter pda = new MySqlDataAdapter();
    MySqlCommandBuilder pbldr = new MySqlCommandBuilder();
    public DataSet trident_data_in = new DataSet();
    public DataSet trident = new DataSet();
    public DataSet trident_data_out = new DataSet();
    public DataSet contacts = new DataSet();
    public DataSet contacts_out = new DataSet();
    public DataTable proj_out;
    public DataTable projcont;
    public DataTable projcont_out;
    public DataTable contact;
    public DataTable pass;
    public DataTable pass_out;
    private Config config;
    private string _username;
    private string _host;
    private string _pass;
    private string _database;
    private int _port;
    protected const string traceout = "trace";
    protected Microsoft.Exchange.WebServices.Authentication.TraceListener tracer = new Microsoft.Exchange.WebServices.Authentication.TraceListener();
    public mysqlcon()
    {

      try
      {
        Stream s;
        #if (DEBUG)
        {
          s = File.Open(Properties.Settings.Default.CONFIGDEV, FileMode.Open);
        }
        #elif (!DEBUG)
        {
          s = File.Open(Properties.Settings.Default.CONFIGDEV, FileMode.Open);
        }
        #endif
        BinaryFormatter b = new BinaryFormatter();
        config = (Config)b.Deserialize(s);
        s.Close();
        config.GetSqlInfo(out _host, out _username, out _pass, out _database, out _port);
      }
      catch
      {

      }
    }

    public void connecttrident()
    {
      //this establishes sql statements for the connection for the mysql trident database
      //and the tables in the mysql trident database
      try
      {
        string[] database = _database.Split(new Char[] { ' ', ',' });
        _port = 3306;
        conn.ConnectionString = "Database=" + database[1] + ";Data Source=" + _host + ";User Id=" + _username + ";Password=" + _pass + ";Port=" + _port.ToString();
        tracer.Trace(traceout, string.Format("ConectionString:Database= {0};Data Source= {1};User Id= {2};Password= {3};Port= {4}",database[1], _host, _username, _pass, _port.ToString()));
        tracer.Trace(traceout, "opening connection");
        conn.Open();
        GC.KeepAlive(conn);
      }
      catch (Exception ex)
      {
        throw new Exception("Open Connection failed", ex);
      }
    }

    //this loads trident from mysql

    public void loadmysqltrident(MySqlCommand tricmd, MySqlDataAdapter trida)
    {
      tricmd.CommandText = "Select * from trident order by trident_id desc limit 1"; tracer.Trace(traceout, tricmd.CommandText);
      tricmd.Connection = conn;
      trida.SelectCommand = tricmd;
      trida.TableMappings.Add("Table", "trident"); tracer.Trace(traceout, "table mappings");
      trida.FillSchema(trident_data_in, SchemaType.Source, "trident"); tracer.Trace(traceout, "fill schema");
      trida.MissingSchemaAction = MissingSchemaAction.AddWithKey;
      trida.Fill(trident, "trident"); tracer.Trace(traceout, "fill trident table");
      tricmd.ExecuteNonQuery();
      trident.AcceptChanges(); tracer.Trace(traceout, "Accepting changes trident table");
    }

    public void updatemysqltrident(MySqlCommand tricmd, MySqlDataAdapter trida)
    {
      tricmdbldr = new MySqlCommandBuilder(trida);
      tricmd.Connection = conn;
      #if (!DEBUG)
      var data = trident.GetChanges();
      trida.Update(data, "trident");
      tricmd.ExecuteNonQuery();
      #elif (DEBUG)
      tracer.Trace(traceout, "DEBUG:: pretending to update Trident MySql tables");
      var data = trident.GetChanges();
      foreach(DataTable table in data.Tables) {
        tracer.Trace(traceout, string.Format("\tTable: {0}",table.TableName));
        tracer.Trace(traceout, "\t\tRow: ");
        using (StreamWriter writer = new StreamWriter(tracer.SaveLocation+table.TableName+".xml",false)) {
          table.WriteXml(writer, XmlWriteMode.DiffGram);
          writer.Close();
        }
      }
      #endif
      conn.Close();
    }

    public void connectcontacts()
    {
      //this establishes sql statements for he connection for the mysql contacts database
      //and the tables in the mysql contacts database
      try
      {
        string[] database = _database.Split(new Char[] { ' ', ',' });
        conn.ConnectionString = "Database=" + database[0] + ";Data Source=" + _host + ";Port=" + _port + ";User Id=" + _username + ";Password=" + _pass;
        conn.Open();
        GC.KeepAlive(conn);
      }
      catch (Exception ex)
      {
        throw new Exception("Open Connection failed", ex);
      }
    }

    public void loadtables(string table)
    {
      tracer.Trace(traceout, "loading tables:");
      contactscmd.CommandText = "Select * from " + table; tracer.Trace(traceout, string.Format("  Table: {0}  \n     Select: {1}", table, contactscmd.CommandText));
      contactscmd.Connection = conn;
      contactsda.SelectCommand = contactscmd;
      contactsda.TableMappings.Add(table, table);
      contactsda.FillSchema(contacts, SchemaType.Source, table);
      contactsda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
      contactsda.Fill(contacts, table); tracer.Trace(traceout, string.Format("fill table  {0}", table));
      contactscmd.ExecuteNonQuery();
    }
    public void updatemysqlp()
    {
      MySqlCommand pcmd = new MySqlCommand();
      pbldr = new MySqlCommandBuilder(pda);
      pass_out = pass.GetChanges();
      pcmd.CommandText = "Select * from p";
      pcmd.Connection = conn;
      pda.SelectCommand = pcmd;
      if (pass_out != null)
      {
        pda.Update(pass_out);
      }
    }

    public void updatemysqlprojects(DataTable proj)
    {
      projbldr = new MySqlCommandBuilder(projda);
      proj_out = proj.GetChanges();
      projcmd.CommandText = "Select * from projects";
      projcmd.Connection = conn;
      projda.SelectCommand = projcmd;
      #if (!DEBUG)
      if (proj_out != null) {
        projda.Update(proj_out);
      }
      #elif (DEBUG)
      tracer.Trace(traceout, "DEBUG:: pretending to update `contacts`.`projects` MySql tables");
      tracer.Trace(traceout, string.Format("\tTable: {0}",proj_out.TableName));
      tracer.Trace(traceout, "\t\tRow: ");
      using (StreamWriter writer = new StreamWriter(tracer.SaveLocation+"projects.xml",false)) {
        proj_out.WriteXml(writer, XmlWriteMode.DiffGram);
        writer.Close();
      }
      #endif
    }

    public void updatemysqlprojectcontacts()
    {
      pcontbldr = new MySqlCommandBuilder(pcontda);
      projcont_out = projcont.GetChanges();
      pcontcmd.CommandText = "Select * from project_contacts";
      pcontcmd.Connection = conn;
      pcontda.SelectCommand = pcontcmd;
      #if (!DEBUG)
      if (projcont_out != null) {
        //HACK: continuing update operation even when there are errors in the row update.
        //Use event RowUpdated to see and fix the errors
        pcontda.ContinueUpdateOnError = true;
        pcontda.Update(projcont_out);
      }
      #elif (DEBUG)
      tracer.Trace(traceout, "DEBUG:: pretending to update `contacts`.`projectcontacts` MySql tables");
      if (projcont_out != null) {
        tracer.Trace(traceout, "\t\tRow: ");
        using (StreamWriter writer = new StreamWriter(tracer.SaveLocation+"projectcontacts.xml",false)) {
          projcont_out.WriteXml(writer, XmlWriteMode.DiffGram);
          writer.Close();
        }
      }
      #endif
    }

    public void disconnect()
    {
      conn.Close();
    }
  }
}
